#!/usr/bin/env python3
# -*- coding: utf-8 -*-

"""
Zabbix 监控脚本：learn_record 表超时订单统计（方案A - 固定配置）
数据库连接参数直接写在脚本中（适合内网/测试环境）

支持的指标：
  overdue_gt5, overdue_gt10, overdue_gt30,
  overdue_recent_gt5, overdue_recent_gt10, overdue_recent_gt30

用法：
  get_learn_record_overdue.py <metric_key>
示例：
  get_learn_record_overdue.py overdue_gt5
"""

import sys
import pymysql

# ===============【数据库配置 - 直接写入】===============
DB_HOST = "localhost"
DB_PORT = 3306
DB_USER = "zabbix_monitor"
DB_PASS = "your_secure_password_here"  # ←←← 请修改为实际密码
DB_NAME = "your_database_name"         # ←←← 请修改为实际库名
# ===================================================

METRICS = {
    "overdue_gt5": """
        SELECT COUNT(*) FROM learn_record
        WHERE status = 2
          AND pre_end_time < end_time
          AND TIMESTAMPDIFF(MINUTE, pre_end_time, end_time) > 5
          AND end_time > NOW() - INTERVAL 5 MINUTE
          AND end_time < NOW() + INTERVAL 1 MINUTE;
    """,
    "overdue_gt10": """
        SELECT COUNT(*) FROM learn_record
        WHERE status = 2
          AND pre_end_time < end_time
          AND TIMESTAMPDIFF(MINUTE, pre_end_time, end_time) > 10
          AND end_time > NOW() - INTERVAL 5 MINUTE
          AND end_time < NOW() + INTERVAL 1 MINUTE;
    """,
    "overdue_gt30": """
        SELECT COUNT(*) FROM learn_record
        WHERE status = 2
          AND pre_end_time < end_time
          AND TIMESTAMPDIFF(MINUTE, pre_end_time, end_time) > 30
          AND end_time > NOW() - INTERVAL 5 MINUTE
          AND end_time < NOW() + INTERVAL 1 MINUTE;
    """,
    "overdue_recent_gt5": """
        SELECT COUNT(*) FROM learn_record
        WHERE status = 1
          AND pre_end_time >= NOW() - INTERVAL 60 MINUTE
          AND pre_end_time < NOW() - INTERVAL 5 MINUTE;
    """,
    "overdue_recent_gt10": """
        SELECT COUNT(*) FROM learn_record
        WHERE status = 1
          AND pre_end_time >= NOW() - INTERVAL 60 MINUTE
          AND pre_end_time < NOW() - INTERVAL 10 MINUTE;
    """,
    "overdue_recent_gt30": """
        SELECT COUNT(*) FROM learn_record
        WHERE status = 1
          AND pre_end_time >= NOW() - INTERVAL 60 MINUTE
          AND pre_end_time < NOW() - INTERVAL 30 MINUTE;
    """,
}


def main():
    if len(sys.argv) != 1:
        metric = sys.argv[1]
    else:
        print("Usage: {} <metric_key>".format(sys.argv[0]), file=sys.stderr)
        sys.exit(1)

    if metric not in METRICS:
        print("Unknown metric: {}".format(metric), file=sys.stderr)
        sys.exit(1)

    sql = METRICS[metric]

    try:
        conn = pymysql.connect(
            host=DB_HOST,
            port=DB_PORT,
            user=DB_USER,
            password=DB_PASS,
            database=DB_NAME,
            charset='utf8mb4',
            connect_timeout=5,
            read_timeout=5,
        )
        with conn.cursor() as cursor:
            cursor.execute(sql)
            result = cursor.fetchone()
            count = result[0] if result else 0
            print(int(count))
    except Exception as e:
        print("DB Error: {}".format(e), file=sys.stderr)
        sys.exit(1)
    finally:
        if 'conn' in locals():
            conn.close()


if __name__ == '__main__':
    main()